import xlrd
import openpyxl
import os
import sys
from utils import check_cell_in_range, trans_coor_to_pos_label, combination_relationships

class Spreadsheet(object):
    def __init__(self, excel_name, extension, sheet_list=None):
        # 电子表格对象
        self.name = excel_name  # 文件名
        self.ext = extension # 扩展
        
        self.sheets = sheet_list
        self.sheet_num = len(self.sheets)

class Sheet(object):
    def __init__(self):
        # sheet 对象
        self.sheet_id = 0  # sheet id
        self.sheet_name = ""  # sheet 名
        self.first_row_num = 0  # 第一行行号
        self.last_row_num = 0  # 第一列列号
        self.first_col_num = 0
        self.last_col_num = 0
        self.all_cell_count = 0  # 所有单元格的数量
        self.frozen_row = 0  # 冻结单元格行号
        self.frozen_col = 0  # 冻结单元格列号
        self.is_freeze = False  # 是否有冻结单元格


class Cell(object):
    def __init__(self, cell: openpyxl.cell.cell.Cell):
        # cell 对象
        self.cell = cell
        self.col_id = cell.col_idx  # 列 id
        self.row_id = cell.row  # 行 id
        self.col_name = cell.column_letter  # 列名
        self.row_name = trans_coor_to_pos_label(self.row_id, "row")  # 行名
        self.coordinate = cell.coordinate
        assert f"{self.col_name}{self.row_name}" == self.coordinate
        self.comment = cell.comment
        self.value = cell.value
        self.internal_value = cell.internal_value
        self.in_table_title = False
        self.in_col_header = False
        self.in_row_header = False
        self.in_data_panel = False
        self.cell_type = 0
        # self.width = 0  # 宽度
        # self.height = 0  # 高度
        # self.have_left_border = False  # 是否有左边框
        # self.have_top_border = False  # 是否有上边框
        # self.have_right_border = False  # 是否有右边框
        # self.have_bottom_border = False  # 是否有下边框
        # self.left_border_type = 1  # 左边框类型
        # self.left_border_color = ""  # 左边框颜色
        # self.top_border_type = 1  # 上边框类型
        # self.top_border_color = ""  # 上边框颜色
        # self.right_border_type = 1  # 右边框类型
        # self.right_border_color = ""  # 右边框颜色
        # self.bottom_border_type = 1  # 下边框类型
        # self.bottom_border_color = ""  # 下边框颜色
        # self.is_non_empty = True  # 是否有内容
        # self.is_fomular = False  # 是否是公式
        # self.data = ""  # 单元格内容
        # self.data_type = ""  # 单元格内容的类型
        # self.have_background = False  # 是否有背景填充
        # self.background_color = ""  # 北京填充颜色
        # self.font_family = ""  # 字体
        # self.font_style = 0  # 字形
        # self.font_size = 9  # 字号
        # self.under_line_type = 0  # 下划线类型
        # self.have_delete_line = False  # 是否有删除线
        # self.superscript = False  # 是否为上标
        # self.subscript = False  # 是否为下表
        # self.font_color = ""  # 字的颜色




    def gen_relationship(self, col_header, row_header, col_header_coors, row_header_coors):
        # 生成关系
        # col_header_coors 表示列头的坐标四元组
        # row_header_coors 表示行头的坐标四元组

        relationship = []
        col_header_relationship = []
        row_header_relationship = []
        if col_header_coors is not None:
            # import pdb;pdb.set_trace()
            if col_header_coors[0] <= self.col_id <= col_header_coors[2]:
                row_index = col_header_coors[1]
                while row_index <= col_header_coors[3]:
                    related_cell = col_header[row_index-col_header_coors[1]][self.col_id-col_header_coors[0]]
                    if related_cell.value != "":
                        col_header_relationship.append(related_cell.internal_value.strip())
                    row_index += 1
        col_header_relationship = combination_relationships(col_header_relationship)
        if row_header_coors is not None:
            if row_header_coors[1] <= self.row_id <= row_header_coors[3]:
                col_index = row_header_coors[0]
                while col_index <= row_header_coors[2]:
                    related_cell = row_header[self.row_id-row_header_coors[1]][col_index-row_header_coors[0]]
                    if related_cell.value != "":
                        relationship.append(related_cell.internal_value.strip())
                    col_index += 1
        row_header_relationship = combination_relationships(row_header_relationship)
        relationship.extend(col_header_relationship)
        relationship.extend(row_header_relationship)
        relationship.append(self.internal_value.strip())
        self.relationship = relationship
    

    def classify_cell(self, col_header_range, row_header_range):
        if check_cell_in_range(self.coordinate, col_header_range):
            self.in_col_header = True
            return
        if check_cell_in_range(self.coordinate, row_header_range):
            self.in_row_header = True
            return
        # 还缺一个判断是否在表头
        self.in_data_panel = True
    
    def format(self):
        return_dict = {
            "values": self.internal_value,
            "coordinate": self.coordinate,
            "col_label": self.col_name,
            "row_label": self.row_name,
            "col_id": self.col_id,
            "row_id": self.row_id,
            "relationships": self.relationship,
            "cell_type":self.cell_type,
        }
        return return_dict
